package simple.xa;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;
import javax.transaction.Status;
import javax.transaction.UserTransaction;

import org.apache.log4j.Logger;

import junit.framework.Assert;

import com.atomikos.icatch.jta.UserTransactionImp;
import com.atomikos.jdbc.AtomikosDataSourceBean;

public class XaAccountDSBeanServicePgSQL {
	// the globally unique resource name for the DB; change if needed
	private static String resourceName = "samples/jdbc/PgSQLJdbcPooledDatabase";
	private static Logger logger = Logger.getLogger(XaAccountDSBeanServicePgSQL.class);
	// the data source, set by getDataSource
	private static AtomikosDataSourceBean ds = null;

	/**
	 * Setup DB tables if needed.
	 */

	public static void checkTables() throws Exception {
		boolean error = false;
		Connection conn = null;
		try {
			conn = getConnection();
		} catch (Exception noConnect) {
			System.err.println("Failed to connect.");
			System.err
					.println("PLEASE MAKE SURE THAT FIRSTSQL IS INSTALLED AND RUNNING");
			throw noConnect;
		}
		try {

			Statement s = conn.createStatement();
			try {
				s.executeQuery("select * from Accounts");

			} catch (SQLException ex) {
				// table not there => create it
				System.err.println("Creating Accounts table...");
				s.executeUpdate("create table Accounts ( "
						+ " account VARCHAR ( 20 ), owner VARCHAR(300), balance DECIMAL (19,0) )");
				for (int i = 0; i < 100; i++) {
					s.executeUpdate("insert into Accounts values ( "
							+ "'account" + i + "' , 'owner" + i + "', 10000 )");
				}
			}
			s.close();
		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			closeConnection(conn, error);

		}

		// That concludes setup

	}

	public static void initTables() throws Exception {
		boolean error = false;
		Connection conn = null;
		try {
			conn = getConnection();
		} catch (Exception noConnect) {
			System.err.println("Failed to connect.");
			System.err
					.println("PLEASE MAKE SURE THAT PGSQL IS RUNNING");
			throw noConnect;
		}
		try {

			Statement s = conn.createStatement();
			System.err.println("Creating Accounts table...");
			s.executeUpdate("create table Accounts ( "
					+ " account VARCHAR ( 20 ), owner VARCHAR(300), balance DECIMAL (19,0) )");
			for (int i = 0; i < 100; i++) {
				s.executeUpdate("insert into Accounts values ( " + "'account"
						+ i + "' , 'owner" + i + "', 10000 )");
			}

			s.close();
		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			closeConnection(conn, error);

		}

		// That concludes setup
	}
	
	public static void dropTables() throws Exception {
		boolean error = false;
		Connection conn = null;
		try {
			conn = getConnection();
		} catch (Exception noConnect) {
			System.err.println("Failed to connect.");
			System.err
					.println("PLEASE MAKE SURE THAT PGSQL IS AND RUNNING");
			throw noConnect;
		}
		try {

			Statement s = conn.createStatement();
			System.err.println("Droping Accounts table...");
			s.execute("drop table Accounts");
			s.close();
		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			closeConnection(conn, error);

		}

	}

	public static DataSource getDataSource() {

		if (ds == null) {
			// Find or construct a datasource instance;
			// this could equally well be a JNDI lookup
			// where available. To keep it simple, this
			// demo merely constructs a new instance.
			ds = new AtomikosDataSourceBean();
			// org.apache.derby.jdbc.EmbeddedXADataSource derbyDs = new
			// org.apache.derby.jdbc.EmbeddedXADataSource();

			org.postgresql.xa.PGXADataSource pgSqlDs = new org.postgresql.xa.PGXADataSource();
			pgSqlDs.setDatabaseName("SilverTest");
			// build.setCreateDatabase("/Users/yudi/Desktop/derbyDb/test");

			pgSqlDs.setUser("yudi");
			pgSqlDs.setPassword("macjaguar");
			pgSqlDs.setServerName("localhost");
			pgSqlDs.setPortNumber(5432);

			Assert.assertNotNull(pgSqlDs);

			ds.setXaDataSource(pgSqlDs);

			// //REQUIRED: the full name of the XA datasource class
			// ds.setXaDataSourceClassName (
			// "org.apache.derby.jdbc.EmbeddedXADataSource" );
			// //REQUIRED: properties to set on the XA datasource class
			// ds.getXaProperties().setProperty("databaseName",
			// "${user.home}/Desktop/derbyDb/test/test");
			// ds.getXaProperties().setProperty("createDataBase", "create");
			// REQUIRED: unique resource name for transaction recovery
			// configuration
			ds.setUniqueResourceName(resourceName);
			// OPTIONAL: what is the pool size?
			ds.setPoolSize(5);
			// OPTIONAL: how long until the pool thread checks liveness of
			// connections?
			ds.setBorrowConnectionTimeout(60);

			// NOTE: the resulting datasource can be bound in JNDI where
			// available
		}
		return ds;
	}

	/**
	 * Utility method to start a transaction and get a connection.
	 * 
	 * @return Connection The connection.
	 */

	public static Connection getConnection() throws Exception {
		DataSource ds = getDataSource();
		Connection conn = null;

		// Retrieve of construct the UserTransaction
		// (can be bound in JNDI where available)
		UserTransaction utx = new UserTransactionImp();
		utx.setTransactionTimeout(60);

		// First, create a transaction
		utx.begin();
		conn = ds.getConnection();

		return conn;

	}

	/**
	 * Utility method to close the connection and terminate the transaction.
	 * This method does all XA related tasks and should be called within a
	 * transaction. When it returns, the transaction will be terminated.
	 * 
	 * @param conn
	 *            The connection.
	 * @param error
	 *            Indicates if an error has occurred or not. If true, the
	 *            transaction will be rolled back. If false, the transaction
	 *            will be committed.
	 */

	public static void closeConnection(Connection conn, boolean error)
			throws Exception {
		if (conn != null)
			conn.close();

		UserTransaction utx = new UserTransactionImp();
		if (utx.getStatus() != Status.STATUS_NO_TRANSACTION) {
			if (error)
				utx.rollback();
			else
				utx.commit();
		} else
			logger.info("WARNING: closeConnection called outside a tx");

	}

	public static long getBalance(int account) throws Exception {
		long res = -1;
		boolean error = false;
		Connection conn = null;

		try {
			conn = getConnection();
			Statement s = conn.createStatement();
			String query = "select balance from Accounts where account='"
					+ "account" + account + "'";
			ResultSet rs = s.executeQuery(query);
			if (rs == null || !rs.next())
				throw new Exception("Account not found: " + account);
			res = rs.getLong(1);
			s.close();
		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			closeConnection(conn, error);
		}
		return res;
	}

	public static String getOwner(int account) throws Exception {
		String res = null;
		boolean error = false;
		Connection conn = null;

		try {
			conn = getConnection();
			Statement s = conn.createStatement();
			String query = "select owner from Accounts where account='account"
					+ account + "'";
			ResultSet rs = s.executeQuery(query);
			if (rs == null || !rs.next())
				throw new Exception("Account not found: " + account);
			res = rs.getString(1);
			s.close();
		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			closeConnection(conn, error);
		}
		return res;
	}

	public static void withdraw(int account, int amount) throws Exception {
		boolean error = false;
		Connection conn = null;

		try {
			conn = getConnection();
			Statement s = conn.createStatement();

			String sql = "update Accounts set balance = balance - " + amount
					+ " where account ='account" + account + "'";
			s.executeUpdate(sql);
			s.close();
		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			closeConnection(conn, error);

		}

	}

	public static void withdrawError(int account, int amount) throws Exception {
		boolean error = false;
		Connection conn = null;

		try {
			conn = getConnection();
			Statement s = conn.createStatement();

			String sql = "update Accounts set balance = balance - " + amount
					+ " where account ='account" + account + "'";
			s.executeUpdate(sql);

			String sql1 = "update Accounts set balance = balance1 - " + amount
					+ " where account ='account" + account + "'";
			s.executeUpdate(sql1);
			s.close();
		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			closeConnection(conn, error);

		}

	}

}
